﻿using Mimi.Models.DataModels;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Mimi.Common
{
    public class StorageManager : IStorageManager
    {
        #region Fields
        private SqliteWrapper sqliteWrapper;
        #endregion

        #region Constants
        private const string c_DatabaseName = "ExpenseManager";
        private const string c_BudgetTableName = "Budget";
        private const string c_ExpenseTableName = "Expense";
        private const string c_OverviewTableName = "Overview";
        private const string c_TableParamFormat = "{0} {1}";
        private const string c_Varchar = "Varchar({0})";
        private const string c_SelectQuerySimpleForm = "select {0} from {1}";
        private const string c_SqlAllDataSymbol = "*";
        private const string c_SqlSetForm = "{0}=\'{1}\'";
        private const string c_IncomeTableName = "Income";
        #endregion

        /// <summary>
        /// Default database
        /// </summary>
        public StorageManager()
        {
            sqliteWrapper = new SqliteWrapper();
        }

        /// <summary>
        /// <see cref="CreateStorageDatabase"/>
        /// </summary>
        public void CreateStorageDatabase()
        {
            sqliteWrapper.CreateSqliteDatabase(c_DatabaseName);
        }

        /// <summary>
        /// <see cref="CreateBudgetTable"/>
        /// </summary>
        public void CreateBudgetTable(IEnumerable<string> propertyList)
        {
            CreateTable(c_BudgetTableName, propertyList);
        }

        /// <summary>
        /// <see cref="CreateExpenseTable"/>
        /// </summary>
        public void CreateExpenseTable(IEnumerable<string> propertyList)
        {
            CreateTable(c_ExpenseTableName, propertyList);
        }

        /// <summary>
        /// <see cref="CreateOverviewTable"/>
        /// </summary>
        /// <param name="propertyList"></param>
        public void CreateOverviewTable(IEnumerable<string> propertyList)
        {
            CreateTable(c_OverviewTableName, propertyList);
        }

        /// <summary>
        /// <see cref="CreateIncomeTable"/>
        /// </summary>
        /// <param name="propertyList"></param>
        public void CreateIncomeTable(IEnumerable<string> propertyList)
        {
            CreateTable(c_IncomeTableName, propertyList);
        }

        /// <summary>
        /// <see cref="AddExpenseData"/>
        /// </summary>
        /// <param name="expense"></param>
        public void AddExpenseData(Expense expense)
        {
            Dictionary<string, string> recordData = new Dictionary<string, string>();

            recordData.Add("ExpenseId", expense.ExpenseId);
            recordData.Add("Amount", expense.Amount.ToString());
            recordData.Add("Description", expense.Description);
            recordData.Add("Date", expense.Date.ToShortDateString());
            recordData.Add("SpentOn", (expense.SpentOn != null) ? expense.SpentOn.PurposeId : "null");

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.InsertRecordIntoTable(c_ExpenseTableName, recordData);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// <see cref="AddBudgetData"/>
        /// </summary>
        /// <param name="purpose"></param>
        public void AddBudgetData(Purpose purpose)
        {
            Dictionary<string, string> recordData = new Dictionary<string, string>();

            recordData.Add("PurposeId", purpose.PurposeId);
            recordData.Add("Description", purpose.Description);
            recordData.Add("Budget", purpose.Budget.ToString());
            recordData.Add("Quota", purpose.Quota.ToString());
            recordData.Add("Currency", purpose.Currency.ToString());
            recordData.Add("Type", purpose.Type.ToString());

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.InsertRecordIntoTable(c_BudgetTableName, recordData);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// <see cref="AddOverviewData"/>
        /// </summary>
        /// <param name="overview"></param>
        public void AddOverviewData(Overview overview)
        {
            Dictionary<string, string> recordData = new Dictionary<string, string>();

            recordData.Add("OverviewIdentity", overview.OverviewIdentity);
            recordData.Add("TotalBalance", overview.TotalBalance);

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.InsertRecordIntoTable(c_OverviewTableName, recordData);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// <see cref="AddIncomeData"/>
        /// </summary>
        /// <param name="income"></param>
        public void AddIncomeData(Income income)
        {
            Dictionary<string, string> recordData = new Dictionary<string, string>();

            recordData.Add("IncomeId", income.IncomeId);
            recordData.Add("Amount", income.Amount.ToString());
            recordData.Add("Date", income.Date.ToShortDateString());
            recordData.Add("Budget", income.Budget);

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.InsertRecordIntoTable(c_IncomeTableName, recordData);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// Create data table
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="propertyList"></param>
        private void CreateTable(string tableName, IEnumerable<string> propertyList)
        {
            List<string> tableParam = new List<string>();

            foreach (string property in propertyList)
            {
                string varchar50Type = string.Format(c_Varchar, "500");
                string paramText = string.Format(c_TableParamFormat, property, varchar50Type);

                tableParam.Add(paramText);
            }

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.CreateSqliteTable(tableName, tableParam);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// <see cref="QueryAllExpenseData"/>
        /// </summary>
        public async Task<IEnumerable<Expense>> QueryAllExpenseData()
        {
            string query = string.Format(c_SelectQuerySimpleForm, c_SqlAllDataSymbol, c_ExpenseTableName);
            List<Expense> result = new List<Expense>();

            await Task.Run(() =>
                {
                    sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
                    DataTable dataTable = sqliteWrapper.QueryData(query);
                    sqliteWrapper.CloseDatabaseConnection();

                    if (dataTable != null && dataTable.Rows != null && dataTable.Rows.Count > 0)
                    {
                        foreach (var row in dataTable.Rows)
                        {
                            DataRow dataRecord = row as DataRow;

                            if (dataRecord != null)
                            {
                                Expense item = new Expense();
                                item.ExpenseId = dataRecord.ItemArray[0].ToString();
                                item.Amount = double.Parse(dataRecord.ItemArray[1].ToString());
                                item.Description = dataRecord.ItemArray[2].ToString();
                                item.Date = DateTime.Parse(dataRecord.ItemArray[3].ToString());
                                item.SpentOn = new Purpose() { PurposeId = dataRecord.ItemArray[4].ToString() };

                                result.Add(item);
                            }
                        }
                    }
                });

            return result;
        }

        /// <summary>
        /// <see cref="QueryAllBudgetData"/>
        /// </summary>
        public async Task<IEnumerable<Purpose>> QueryAllBudgetData()
        {
            string query = string.Format(c_SelectQuerySimpleForm, c_SqlAllDataSymbol, c_BudgetTableName);
            List<Purpose> result = new List<Purpose>();

            await Task.Run(() =>
            {
                sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
                DataTable dataTable = sqliteWrapper.QueryData(query);
                sqliteWrapper.CloseDatabaseConnection();

                if (dataTable != null && dataTable.Rows != null && dataTable.Rows.Count > 0)
                {
                    foreach (var row in dataTable.Rows)
                    {
                        DataRow dataRecord = row as DataRow;

                        if (dataRecord != null)
                        {
                            Purpose item = new Purpose();
                            item.PurposeId = dataRecord.ItemArray[0].ToString();
                            item.Description = dataRecord.ItemArray[1].ToString();
                            item.Budget = double.Parse(dataRecord.ItemArray[2].ToString());
                            item.Quota = double.Parse(dataRecord.ItemArray[3].ToString());
                            item.Currency = dataRecord.ItemArray[4].ToString() == "USD" ? Currency.USD : Currency.VND;
                            item.Type = dataRecord.ItemArray[5].ToString() == "Percentage" ? PurposeUnitValueType.Percentage : PurposeUnitValueType.Static;

                            result.Add(item);
                        }
                    }
                }
            });

            return result;
        }

        /// <summary>
        /// <see cref="QueryOverviewData"/>
        /// </summary>
        /// <returns></returns>
        public Overview QueryOverviewData()
        {
            string query = string.Format(c_SelectQuerySimpleForm, c_SqlAllDataSymbol, c_OverviewTableName);
            Overview result = null;

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            DataTable dataTable = sqliteWrapper.QueryData(query);
            sqliteWrapper.CloseDatabaseConnection();

            if (dataTable != null && dataTable.Rows != null && dataTable.Rows.Count > 0)
            {
                result = new Overview();

                foreach (var row in dataTable.Rows)
                {
                    DataRow dataRecord = row as DataRow;

                    if (dataRecord != null)
                    {
                        result.OverviewIdentity = dataRecord.ItemArray[0].ToString();
                        result.TotalBalance = dataRecord.ItemArray[1].ToString();
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// <see cref="QueryIncomeData"/>
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<Income>> QueryIncomeData()
        {
            List<Income> results = new List<Income>();

            string query = string.Format(c_SelectQuerySimpleForm, c_SqlAllDataSymbol, c_IncomeTableName);

            await Task.Run(() =>
            {
                sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
                DataTable dataTable = sqliteWrapper.QueryData(query);
                sqliteWrapper.CloseDatabaseConnection();

                if (dataTable != null && dataTable.Rows != null && dataTable.Rows.Count > 0)
                {
                    foreach (var row in dataTable.Rows)
                    {
                        DataRow dataRecord = row as DataRow;

                        if (dataRecord != null)
                        {
                            Income item = new Income();

                            item.IncomeId = dataRecord.ItemArray[0].ToString();
                            item.Amount = double.Parse(dataRecord.ItemArray[1].ToString());
                            item.Budget = dataRecord.ItemArray[2].ToString();
                            item.Date = DateTime.Parse(dataRecord.ItemArray[3].ToString());

                            results.Add(item);
                        }
                    }
                }
            });

            return results;
        }

        /// <summary>
        /// <see cref="UpdateBudgetRowData"/>
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="purpose"></param>
        public void UpdateBudgetRowData(Purpose purpose)
        {
            List<string> newValues = new List<string>();

            newValues.Add(string.Format(c_SqlSetForm, "PurposeId", purpose.PurposeId));
            newValues.Add(string.Format(c_SqlSetForm, "Description", purpose.Description));
            newValues.Add(string.Format(c_SqlSetForm, "Budget", purpose.Budget.ToString()));
            newValues.Add(string.Format(c_SqlSetForm, "Quota", purpose.Quota.ToString()));
            newValues.Add(string.Format(c_SqlSetForm, "Currency", purpose.Currency.ToString()));
            newValues.Add(string.Format(c_SqlSetForm, "Type", purpose.Type.ToString()));

            string condition = string.Format(c_SqlSetForm, "PurposeId", purpose.PurposeId);

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.UpdateRecordData(c_BudgetTableName, condition, newValues);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// <see cref="UpdateOverviewRowData"/>
        /// </summary>
        /// <param name="overview"></param>
        public void UpdateOverviewRowData(Overview overview)
        {
            List<string> newValues = new List<string>();

            newValues.Add(string.Format(c_SqlSetForm, "OverviewIdentity", overview.OverviewIdentity));
            newValues.Add(string.Format(c_SqlSetForm, "TotalBalance", overview.TotalBalance));

            string condition = string.Format(c_SqlSetForm, "OverviewIdentity", overview.OverviewIdentity);

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.UpdateRecordData(c_OverviewTableName, condition, newValues);
            sqliteWrapper.CloseDatabaseConnection();
        }

        /// <summary>
        /// <see cref="RemoveFromIncomeTable"/>
        /// </summary>
        public void RemoveFromIncomeTable(Income income)
        {
            string condition = string.Format(c_SqlSetForm, "IncomeId", income.IncomeId);

            sqliteWrapper.ConnectToSqliteDatabase(c_DatabaseName);
            sqliteWrapper.DeleteRecord(c_IncomeTableName, condition);
            sqliteWrapper.CloseDatabaseConnection();
        }
    }
}
